﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using VideoClassLibrary.Model;

namespace VideoClassLibrary.BusinessLogicLayer
{
    public class NewsModule
    {
        #region  成员方法
        /// <summary>
        /// 修改新闻状态
        /// </summary>
        /// <param name="schoolId">所属学校</param>
        /// <param name="IsPublished">状态</param>
        /// <param name="Nid">新闻id</param>
        /// <returns></returns>
        public DataSet UpdateNewsIsPublished(int schoolId, int IsPublished, int Nid)
        {
            string sql = "update V_News set  IsPublished=@IsPublished where  ID=@nid and schoolid=@schoolId";
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(dbCommand, "nid", DbType.Int32, Nid);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Boolean, IsPublished);
            db.AddInParameter(dbCommand, "schoolid", DbType.Int32, schoolId);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int AddNews(V_News model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_News(");
            strSql.Append("NewsTitle,NewsContent,SchoolId,IsPublished,categoryid,CreaterId,CreaterName,CreatedTime,UpdateTime,NewsAuthor,NewsImage)");

            strSql.Append(" values (");
            strSql.Append("@NewsTitle,@NewsContent,@SchoolId,@IsPublished,@categoryid,@CreaterId,@CreaterName,@CreatedTime,@UpdateTime,@NewsAuthor,@NewsImage)");
            strSql.Append(";select @@IDENTITY");
                        Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "NewsTitle", DbType.String, model.NewsTitle);
            db.AddInParameter(dbCommand, "NewsContent", DbType.String, model.NewsContent);
            db.AddInParameter(dbCommand, "SchoolId", DbType.Int32, model.SchoolId);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Int32, model.IsPublished);
            db.AddInParameter(dbCommand, "categoryid", DbType.Int32, model.CategoryId);
            db.AddInParameter(dbCommand, "CreaterId", DbType.Int32, model.CreaterId);
            db.AddInParameter(dbCommand, "CreaterName", DbType.String, model.CreaterName);
            db.AddInParameter(dbCommand, "CreatedTime", DbType.DateTime, model.CreatedTime);
            db.AddInParameter(dbCommand, "UpdateTime", DbType.DateTime, model.UpdateTime);
            db.AddInParameter(dbCommand, "NewsAuthor", DbType.AnsiString, model.NewsAuthor);
            db.AddInParameter(dbCommand, "NewsImage", DbType.AnsiString, model.NewsImage);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void UpdateNews(V_News model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_News set ");
            strSql.Append("NewsTitle=@NewsTitle,");
            strSql.Append("NewsContent=@NewsContent,");
            strSql.Append("CategoryId=@CategoryId,");
            strSql.Append("SchoolId=@SchoolId,");
            strSql.Append("IsPublished=@IsPublished,");
            //strSql.Append("CreaterId=@CreaterId,");
            strSql.Append("UpdateTime=@UpdateTime,");
            strSql.Append("NewsAuthor=@NewsAuthor,");
            strSql.Append("NewsImage=@NewsImage");
            strSql.Append(" where ID=@ID ");
                        Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, model.ID);
            db.AddInParameter(dbCommand, "NewsTitle", DbType.String, model.NewsTitle);
            db.AddInParameter(dbCommand, "NewsContent", DbType.String, model.NewsContent);
            db.AddInParameter(dbCommand, "CategoryId", DbType.Int32, model.CategoryId);
            db.AddInParameter(dbCommand, "SchoolId", DbType.Int32, model.SchoolId);
            db.AddInParameter(dbCommand, "IsPublished", DbType.Int32, model.IsPublished);
            //db.AddInParameter(dbCommand, "CreaterId", DbType.Int32, model.CreaterId);
            db.AddInParameter(dbCommand, "UpdateTime", DbType.DateTime, model.UpdateTime);
            db.AddInParameter(dbCommand, "NewsAuthor", DbType.AnsiString, model.NewsAuthor);
            db.AddInParameter(dbCommand, "NewsImage", DbType.AnsiString, model.NewsImage);
            db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 更新图片
        /// </summary>
        public void UpdateNews(int id,string image)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_News set ");
            strSql.Append("NewsImage=@NewsImage");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, id);
            db.AddInParameter(dbCommand, "NewsImage", DbType.AnsiString, image);
            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void DeleteNewsbyNid(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from V_News ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, ID);
            db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 根据schoolID获取对应学校的新闻列表，schoolId=0为视频网站的新闻
        /// </summary>
        public DataSet GetNewsListByWhere(int schoolId, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select V_News.ID,NewsTitle,V_NewsCategory.title,NewsContent,CategoryId,Title,IsPublished,CreaterId,CreaterName,V_News.CreatedTime,V_News.UpdateTime,V_News.SchoolId,NewsAuthor,NewsImage from V_News ");
            strSql.Append("join V_NewsCategory on V_News.CategoryId=V_NewsCategory.id ");
            strSql.Append(" where V_News.SchoolId=@ID  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append( strWhere);
            }
            strSql.AppendLine(" Order by CreatedTime Desc");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, schoolId);
            return db.ExecuteDataSet(dbCommand);
        }

        ///// <summary>
        ///// 获得新闻数据列表
        ///// </summary>
        //public DataSet GetNewsListByWhere(string strWhere)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select ID,NewsTitle,NewsContent,CategoryId,SchoolId,IsPublished,CreaterId,CreaterName,CreatedTime,UpdateTime ");
        //    strSql.Append(" FROM V_News ");
        //    if (strWhere.Trim() != "")
        //    {
        //        strSql.Append(" where " + strWhere);
        //    }
        //    strSql.AppendLine(" Order by CreatedTime Desc");
        //                Database db = VDataBase.CreateDatabase();
        //    return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        //}


        /// <summary>
        /// 获得数据列表（比DataSet效率高，推荐使用）
        /// </summary>
        //public List<V_News> GetListArray(string strWhere)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select ID,NewsTitle,NewsContent,CategoryId,SchoolId,IsPublished,CreaterId,CreaterName,CreatedTime,UpdateTime ");
        //    strSql.Append(" FROM V_News ");
        //    if (strWhere.Trim() != "")
        //    {
        //        strSql.Append(" where " + strWhere);
        //    }
        //    List<V_News> list = new List<V_News>();
        //                Database db = VDataBase.CreateDatabase();
        //    using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
        //    {
        //        while (dataReader.Read())
        //        {
        //            list.Add(ReaderBind(dataReader));
        //        }
        //    }
        //    return list;
        //}
        /// <summary>
        /// 对象实体绑定数据
        /// </summary>
        public V_News ReaderBind(IDataReader dataReader)
        {
            V_News model = new V_News();
            object ojb;
            ojb = dataReader["ID"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ID = (int)ojb;
            }
            model.NewsTitle = dataReader["NewsTitle"].ToString();
            model.NewsContent = dataReader["NewsContent"].ToString();
            ojb = dataReader["CategoryId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CategoryId = (int)ojb;
            }
            ojb = dataReader["SchoolId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.SchoolId = (int)ojb;
            }
            ojb = dataReader["IsPublished"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.IsPublished = (int)ojb;
            }
            ojb = dataReader["CreaterId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CreaterId = (int)ojb;
            }
            model.CreaterName = dataReader["CreaterName"].ToString();
            ojb = dataReader["CreatedTime"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CreatedTime = (DateTime)ojb;
            }
            ojb = dataReader["UpdateTime"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.UpdateTime = (DateTime)ojb;
            }
            ojb = dataReader["NewsAuthor"].ToString();
            ojb = dataReader["NewsImage"].ToString();
      
            return model;
        }
        /// <summary>
        /// 获取所有新闻标题
        /// </summary>
        /// <returns></returns>
        public DataSet QueryNewsTitle(string schoolid,int topCount)
        {
            StringBuilder sqlStr = new StringBuilder();
            if (topCount > 0)
            {
                sqlStr.Append("select top(" + topCount + ") id,newstitle,NewsContent,CategoryId,SchoolId,IsPublished,CreaterId,CreaterName,CreatedTime,UpdateTime,NewsAuthor,NewsImage from v_news ");
            }
            else
            {
                sqlStr.Append("select  id,newstitle,NewsContent,CategoryId,SchoolId,IsPublished,CreaterId,CreaterName,CreatedTime,UpdateTime,NewsAuthor,NewsImage from v_news ");
            }
        
            if (schoolid != "")
            {
                sqlStr.Append(" where schoolid=" + schoolid);
            }
            //if (id != "")
            //{

            //    sqlStr.Append(" where id=" + id);
            //}
            sqlStr.Append("order by ID desc");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, sqlStr.ToString());
        }

        /// <summary>
        /// 根据NewsId获取一条新闻的详细信息
        /// </summary>
        /// <param name="newsId"></param>
        /// <returns></returns>
        public V_News GetNewDetailByNewsId(int newsId)
        {
            StringBuilder sqlText = new StringBuilder();
            sqlText.AppendLine("select V_News.ID,NewsTitle,V_NewsCategory.title,NewsContent,CategoryId,IsPublished,CreaterId,CreaterName,V_News.CreatedTime,V_News.UpdateTime,V_News.SchoolId,NewsAuthor,NewsImage from V_News ");
            sqlText.AppendLine(" join V_NewsCategory on V_News.CategoryId=V_NewsCategory.id  ");
            sqlText.AppendLine(" where V_News.ID= "+newsId);
            Database db = VDataBase.CreateDatabase();
            V_News model = null;
            using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, sqlText.ToString()))
            {
                if (dataReader.Read())
                {
                    model = ReaderBind(dataReader);
                }
            }

            return model;
        }
        #endregion
    }
}
